{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import load_workbook\n",
    "\n",
    "# 加载工作簿\n",
    "wb = load_workbook('C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\1\\\\1、产品信息表.xlsx')\n",
    "# 选择工作表\n",
    "sheet = wb['配件信息']\n",
    "\n",
    "# 设置行高，例如将第2行的行高设置为50\n",
    "sheet.row_dimensions[2].height = 50\n",
    "\n",
    "# 设置列宽，例如将B列的列宽设置为20\n",
    "sheet.column_dimensions['B'].width = 20\n",
    "\n",
    "# 保存工作簿\n",
    "wb.save('C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\1\\\\1、产品信息表.xlsx')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import load_workbook\n",
    "\n",
    "# 加载工作簿\n",
    "wb = load_workbook('C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\1\\\\2、新能源汽车备案信息.xlsx')\n",
    "\n",
    "# 遍历所有工作表\n",
    "for sheet in wb.sheetnames:\n",
    "    ws = wb[sheet]\n",
    "    # 设置所有行的行高，例如设置为30\n",
    "    for row in ws.iter_rows():\n",
    "        for cell in row:\n",
    "            ws.row_dimensions[cell.row].height = 30\n",
    "    # 设置所有列的列宽，例如设置为15\n",
    "    for col in ws.iter_cols():\n",
    "        for cell in col:\n",
    "            ws.column_dimensions[cell.column_letter].width = 15\n",
    "\n",
    "# 保存工作簿\n",
    "wb.save('C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\1\\\\2、新能源汽车备案信息.xlsx')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from openpyxl import load_workbook\n",
    "\n",
    "# 工作簿所在的文件夹\n",
    "folder_path = 'C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\1\\\\3、区域销售统计'\n",
    "\n",
    "# 遍历文件夹内的所有工作簿\n",
    "for filename in os.listdir(folder_path):\n",
    "    if filename.endswith('.xlsx'):\n",
    "        # 完整的工作簿路径\n",
    "        file_path = os.path.join(folder_path, filename)\n",
    "        # 加载工作簿\n",
    "        wb = load_workbook(file_path)\n",
    "        \n",
    "        # 遍历所有工作表\n",
    "        for sheet in wb.sheetnames:\n",
    "            ws = wb[sheet]\n",
    "            # 设置行高和列宽\n",
    "            for row in ws.iter_rows():\n",
    "                for cell in row:\n",
    "                    ws.row_dimensions[cell.row].height = 30\n",
    "            for col in ws.iter_cols():\n",
    "                for cell in col:\n",
    "                    ws.column_dimensions[cell.column_letter].width = 15\n",
    "        \n",
    "        # 保存工作簿\n",
    "        wb.save(file_path)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import load_workbook\n",
    "\n",
    "# 加载工作簿\n",
    "wb = load_workbook('C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\2\\\\1、工资表1.xlsx')\n",
    "# 选择工作表\n",
    "sheet = wb.active\n",
    "\n",
    "# 获取工作表的最大行数\n",
    "max_row = sheet.max_row\n",
    "\n",
    "# 从最后一行开始，每隔一行插入空白行\n",
    "for row in range(max_row, 1, -1):  # 从最大行数开始，逆序遍历\n",
    "    sheet.insert_rows(row)  # 插入空白行\n",
    "\n",
    "# 保存工作簿\n",
    "wb.save('C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\2\\\\1、工资表1.xlsx')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import load_workbook\n",
    "\n",
    "# 加载工作簿\n",
    "wb = load_workbook('C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\2\\\\2、产品信息表.xlsx')\n",
    "# 选择工作表\n",
    "sheet = wb.active\n",
    "\n",
    "# 要追加的数据列表\n",
    "data_to_append = ['A1', 'B1', 'C1']\n",
    "\n",
    "# 追加数据到工作表的下一行\n",
    "sheet.append(data_to_append)\n",
    "\n",
    "# 保存工作簿\n",
    "wb.save('C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\2\\\\2、产品信息表.xlsx')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from openpyxl import load_workbook\n",
    "\n",
    "# 工作簿所在的文件夹\n",
    "folder_path = 'C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\2\\\\3区域销售统计'\n",
    "\n",
    "# 要追加的数据列表\n",
    "data_to_append = ['A1', 'B1', 'C1']\n",
    "\n",
    "# 遍历文件夹内的所有工作簿\n",
    "for filename in os.listdir(folder_path):\n",
    "    if filename.endswith('.xlsx'):\n",
    "        # 完整的工作簿路径\n",
    "        file_path = os.path.join(folder_path, filename)\n",
    "        # 加载工作簿\n",
    "        wb = load_workbook(file_path)\n",
    "        \n",
    "        # 选择同名工作表\n",
    "        if 'Sheet1' in wb.sheetnames:\n",
    "            sheet = wb['Sheet1']\n",
    "            \n",
    "            # 追加数据到工作表的下一行\n",
    "            sheet.append(data_to_append)\n",
    "        \n",
    "        # 保存工作簿\n",
    "        wb.save(file_path)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import load_workbook\n",
    "\n",
    "# 加载工作簿\n",
    "wb = load_workbook('C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\3\\\\1、产品规格表.xlsx')\n",
    "# 选择工作表\n",
    "sheet = wb.active\n",
    "\n",
    "# 要拆分的列，例如A列（表示为1）\n",
    "column_number = 1\n",
    "\n",
    "# 存储拆分后的列的字母\n",
    "split_columns = []\n",
    "\n",
    "# 遍历A列中的每个单元格\n",
    "for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=column_number, max_col=column_number):\n",
    "    for cell in row:\n",
    "        # 如果单元格中有逗号分隔的数据\n",
    "        if cell.value and ',' in str(cell.value):\n",
    "            # 拆分数据\n",
    "            split_data = str(cell.value).split(',')\n",
    "            # 获取当前单元格的列字母\n",
    "            current_column = chr(column_number + ord('A') - 1)\n",
    "            # 遍历拆分后的数据，将其写入新的列中\n",
    "            for i, data in enumerate(split_data):\n",
    "                # 计算新的列字母\n",
    "                new_column = chr(column_number + i)\n",
    "                # 如果新的列不存在，创建列\n",
    "                if new_column not in sheet.column_dimensions:\n",
    "                    sheet.column_dimensions[new_column] = sheet.column_dimensions[current_column]\n",
    "                # 写入数据\n",
    "                sheet[f'{new_column}{cell.row}'] = data\n",
    "                # 记录新的列字母\n",
    "                if new_column not in split_columns:\n",
    "                    split_columns.append(new_column)\n",
    "\n",
    "# 删除原始的A列\n",
    "sheet.delete_cols(column_number)\n",
    "\n",
    "# 保存工作簿\n",
    "wb.save('C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\3\\\\1、产品规格表.xlsx')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'tuple' object has no attribute 'max_column'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-17-9de45ebb0db3>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     13\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     14\u001b[0m \u001b[1;31m# 遍历工作表的行\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 15\u001b[1;33m \u001b[1;32mfor\u001b[0m \u001b[0mrow\u001b[0m \u001b[1;32min\u001b[0m \u001b[0msheet\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0miter_rows\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mmin_row\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmax_col\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mmax\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msheet\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcolumn\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmax_column\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mcolumn\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mcolumns_to_merge\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmin_col\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mmin\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msheet\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcolumn\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmin_column\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mcolumn\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mcolumns_to_merge\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     16\u001b[0m     \u001b[0mmerged_data\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     17\u001b[0m     \u001b[1;31m# 遍历要合并的列\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m<ipython-input-17-9de45ebb0db3>\u001b[0m in \u001b[0;36m<genexpr>\u001b[1;34m(.0)\u001b[0m\n\u001b[0;32m     13\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     14\u001b[0m \u001b[1;31m# 遍历工作表的行\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 15\u001b[1;33m \u001b[1;32mfor\u001b[0m \u001b[0mrow\u001b[0m \u001b[1;32min\u001b[0m \u001b[0msheet\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0miter_rows\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mmin_row\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmax_col\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mmax\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msheet\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcolumn\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmax_column\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mcolumn\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mcolumns_to_merge\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmin_col\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mmin\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msheet\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcolumn\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmin_column\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mcolumn\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mcolumns_to_merge\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     16\u001b[0m     \u001b[0mmerged_data\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     17\u001b[0m     \u001b[1;31m# 遍历要合并的列\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mAttributeError\u001b[0m: 'tuple' object has no attribute 'max_column'"
     ]
    }
   ],
   "source": [
    "from openpyxl import load_workbook\n",
    "\n",
    "# 加载工作簿\n",
    "wb = load_workbook('C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\3\\\\2、产品规格表1.xlsx')\n",
    "# 选择工作表\n",
    "sheet = wb.active\n",
    "\n",
    "# 要合并的列，例如A列和B列\n",
    "columns_to_merge = ['A', 'B']\n",
    "\n",
    "# 存储合并后的列的字母\n",
    "merged_column = 'C'\n",
    "\n",
    "# 遍历工作表的行\n",
    "for row in sheet.iter_rows(min_row=1, max_col=max(sheet[column].max_column for column in columns_to_merge), min_col=min(sheet[column].min_column for column in columns_to_merge)):\n",
    "    merged_data = []\n",
    "    # 遍历要合并的列\n",
    "    for cell in row:\n",
    "        # 如果单元格有值，添加到合并数据列表中\n",
    "        if cell.value is not None:\n",
    "            merged_data.append(str(cell.value))\n",
    "    # 将合并后的数据写入新的列\n",
    "    sheet[f'{merged_column}{cell.row}'] = ' | '.join(merged_data)\n",
    "\n",
    "# 删除原始的A列和B列\n",
    "for column in columns_to_merge:\n",
    "    sheet.delete_cols(sheet[column].min_column)\n",
    "\n",
    "# 保存工作簿\n",
    "wb.save('C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\3\\\\2、产品规格表1.xlsx')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import load_workbook\n",
    "\n",
    "# 加载工作簿\n",
    "wb = load_workbook('C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\3\\\\2、产品规格表1.xlsx')\n",
    "# 选择工作表\n",
    "sheet = wb.active\n",
    "\n",
    "# 要合并的列，例如A列和B列（表示为1和2）\n",
    "columns_to_merge = [1, 2]\n",
    "\n",
    "# 遍历工作表的行\n",
    "for row in sheet.iter_rows(min_row=1, max_col=max(columns_to_merge), min_col=min(columns_to_merge)):\n",
    "    merged_data = []\n",
    "    # 遍历要合并的列\n",
    "    for column in columns_to_merge:\n",
    "        cell = sheet.cell(row=row[0].row, column=column)\n",
    "        # 如果单元格有值，添加到合并数据列表中\n",
    "        if cell.value is not None:\n",
    "            merged_data.append(str(cell.value))\n",
    "    # 将合并后的数据写入新的列\n",
    "    sheet[f'C{row[0].row}'] = ' | '.join(merged_data)\n",
    "\n",
    "# 保存工作簿\n",
    "wb.save('C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\3\\\\2、产品规格表1.xlsx')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import load_workbook\n",
    "\n",
    "# 加载工作簿\n",
    "wb = load_workbook('C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\4\\\\1、销售表.xlsx')\n",
    "# 选择工作表\n",
    "sheet = wb.active\n",
    "\n",
    "# 要替换的数据\n",
    "old_data = '离合器'\n",
    "new_data = '刹车片'\n",
    "\n",
    "# 遍历工作表中的所有单元格\n",
    "for row in sheet.iter_rows():\n",
    "    for cell in row:\n",
    "        if cell.value == old_data:\n",
    "            cell.value = new_data\n",
    "\n",
    "# 保存工作簿\n",
    "wb.save('C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\4\\\\1、销售表.xlsx')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import load_workbook\n",
    "\n",
    "# 加载工作簿\n",
    "wb = load_workbook('C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\4\\\\2、办公用品采购表.xlsx')\n",
    "\n",
    "# 要替换的数据\n",
    "old_data = '固体胶'\n",
    "new_data = '透明胶带'\n",
    "\n",
    "# 遍历所有工作表\n",
    "for sheet in wb.sheetnames:\n",
    "    ws = wb[sheet]\n",
    "    # 遍历工作表中的所有单元格\n",
    "    for row in ws.iter_rows():\n",
    "        for cell in row:\n",
    "            if cell.value == old_data:\n",
    "                cell.value = new_data\n",
    "\n",
    "# 保存工作簿\n",
    "wb.save('C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\4\\\\2、办公用品采购表.xlsx')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import load_workbook\n",
    "\n",
    "# 加载工作簿\n",
    "wb = load_workbook('C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\4\\\\3、销售表.xlsx')\n",
    "# 选择工作表\n",
    "sheet = wb.active\n",
    "\n",
    "# 要替换的数据\n",
    "old_data = '离合器'\n",
    "new_data = '刹车片'\n",
    "\n",
    "# 遍历C列中的所有单元格\n",
    "for row in sheet.iter_rows(min_col=3, max_col=3):  # C列是第3列\n",
    "    for cell in row:\n",
    "        if cell.value == old_data:\n",
    "            cell.value = new_data\n",
    "\n",
    "# 保存工作簿\n",
    "wb.save('C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\4\\\\3、销售表.xlsx')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import load_workbook\n",
    "\n",
    "# 加载工作簿\n",
    "wb = load_workbook('C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\5\\\\2、订单金额表.xlsx')\n",
    "# 选择工作表\n",
    "sheet = wb.active\n",
    "\n",
    "# 合并竖直方向上内容相同的连续单元格\n",
    "def merge_vertical_cells(sheet):\n",
    "    for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=sheet.max_column):\n",
    "        for cell in row:\n",
    "            if cell.value:\n",
    "                sheet.merge_cells(start_row=cell.row, end_row=cell.row, start_column=cell.column, end_column=cell.column)\n",
    "\n",
    "# 执行合并单元格的函数\n",
    "merge_vertical_cells(sheet)\n",
    "\n",
    "# 保存工作簿\n",
    "wb.save('C:\\\\Users\\\\Administrator\\\\Desktop\\\\实验10数据\\\\5\\\\2、订单金额表.xlsx')\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
